package com.li.springbootproject.temptableuse.service.impl;

import com.li.springbootproject.domain.User;
import com.li.springbootproject.domain.common.BaseConst;
import com.li.springbootproject.mapper.CommonMapper;
import com.li.springbootproject.temptableuse.mapper.OracleMapper;
import com.li.springbootproject.temptableuse.service.OracleService;
import com.lidong.toolkit.utils.ListUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author LiDong
 * @version 1.0.0
 * @createTime 10/12/2022 11:06 AM
 */
@Service
public class OracleServiceImpl implements OracleService {

    private static final Logger logger = LoggerFactory.getLogger(OracleServiceImpl.class);

    @Autowired
    private OracleMapper oracleMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private CommonMapper commonMapper;

    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;

    @Autowired
    private TransactionDefinition transactionDefinition;

    /**
     * Mysql 临时表使用测试
     */
    @Override
    public void tempTableInsert() {
        // 手动开启事务：基础数据组织机构数据量过多的时候会写临时表 left join 查询所以开启手动提交事务
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
        try {
            // 创建临时表
            String createSql = "DECLARE NUM INTEGER;\n" +
                    "BEGIN\n" +
                    "\tSELECT COUNT(*) INTO NUM FROM USER_ALL_TABLES WHERE TABLE_NAME='TEMPUSER';\n" +
                    "IF NUM=0\n" +
                    "THEN\n" +
                    "EXECUTE IMMEDIATE 'CREATE  GLOBAL TEMPORARY TABLE TEMPUSER\n" +
                    "   (ID VARCHAR2(36 CHAR), REQUESTID NVARCHAR2(36),CODE NVARCHAR2(30),\n" +
                    "       COLUMNNAME VARCHAR2(50 CHAR),DATATYPE VARCHAR2(30 CHAR)\n" +
                    "   ) ON COMMIT DELETE ROWS';\n" +
                    "END IF;\n" +
                    "END;";
            oracleMapper.executeSql(createSql);

            // 往临时表写数据
            List<User> userList = getUserList();
            List<List<User>> lists = ListUtils.splitList(userList, BaseConst.TEN_THOUSAND);
            for (List<User> list : lists) {
                oracleMapper.insertBatch(list);
            }

            List<Map<String, Object>> list1 = oracleMapper.listSql("select * from tempuser");
            // 使用临时表查询数据
            List<Map<String, Object>> list2 = oracleMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
            List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
            queryTest1();
            // 删除临时表
            String dropSql = "DECLARE\n" +
                    "\tNUM NUMBER;\n" +
                    "BEGIN\n" +
                    "\tSELECT COUNT(1) INTO NUM FROM\n" +
                    "\tUSER_TABLES WHERE TABLE_NAME = UPPER('TEMPUSER') ;\n" +
                    "IF NUM > 0 THEN\n" +
                    "    EXECUTE IMMEDIATE 'DROP TABLE TEMPUSER' ;\n" +
                    "END IF;\n" +
                    "END;";
            oracleMapper.executeSql(dropSql);
        } finally {
            dataSourceTransactionManager.rollback(transactionStatus);
        }

    }

    private void queryTest1() {
        List<Map<String, Object>> list1 = oracleMapper.listSql("select * from tempuser");
        // 使用临时表查询数据
        List<Map<String, Object>> list2 = oracleMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        List<Map<String, Object>> list3 = commonMapper.listSql("select * from tempuser left join t_car on tempuser.id = t_car.id");
        logger.info("queryTest1");
    }

    /**
     * 获取 10 万用户
     *
     * @return List
     */
    private List<User> getUserList() {
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setUsername("user" + i);
            user.setPassword("user" + i);
            userList.add(user);
        }
        return userList;
    }
}
